package com.control;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.db.DBConnect;
import com.model.Comment;

public class CommentControl {
	
	/**
	 * 插入意见
	 * @param comment
	 * @return
	 */
	public boolean insertComment(Comment comment){
		DBConnect dbc=new DBConnect();
		String sql="{call insertComment("+comment.getStu_id()+"," +
				"'"+comment.getComment_content()+"')}";
		/*
		 * String sql="Insert into comment(stu_id,comment_content,comment_time)"+
		 *"values("+comment.getStu_id()+",'"+comment.getComment_content()+"',getDate())";
		*/
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		}
		catch(SQLException e){
			e.getStackTrace();
			return false;
		}
		finally{
			dbc.close();
		}
	}

	/**
	 * 删除意见
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteComment(int comment_id) throws SQLException{
		DBConnect dbc=new DBConnect();
		String sql="{call deleteComment("+comment_id+")}";
		/*
		 * String sql="Delete from comment where comment_id="+id+ " ";
		 */
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		}
		finally{
			dbc.close();
		}
	}
	
	/**
	 * 根据评论id获取意见的内容
	 * @param comment_id
	 * @return
	 */
	public String getCommentContent(int comment_id){
		DBConnect dbc=new DBConnect();
		String sql="{call getCommentContent("+comment_id+")}";
		/*
		 * String sql="Select comment_content from comment where comment_id='"+comment_id+"'";
		 */
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			if(rs.next())
				return rs.getString("comment_content");
		}catch(SQLException e){
			e.getStackTrace();
			return null;
		}finally{
			dbc.close();
		}
		return null;
	}

	/**
	 * 获取所有意见的信息
	 * @return
	 */
	public ArrayList<Comment> getCommentInformation(){
		DBConnect dbc=new DBConnect();
		ArrayList<Comment> commentlist=new ArrayList<Comment>();
		String sql="{call getCommentInformation()}";
		/*
		 * String sql="Select * from comment,student where comment.stu_id=student.stu_id";
		 */
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				Comment com=new Comment();
				com.setComment_id(rs.getInt("comment_id"));
				com.setStu_id(rs.getInt("stu_id"));
				com.setStu_name(rs.getString("stu_name"));
				com.setStu_college(rs.getString("stu_college"));
				com.setStu_major(rs.getString("stu_major"));
				com.setStu_tel(rs.getString("stu_tel"));
				com.setComment_content(rs.getString("comment_content"));
				String str = rs.getString("comment_time");
				if(str != null)
					com.setComment_time(str.substring(0, 16));
				commentlist.add(com);
			}
			return commentlist;
		}catch(SQLException e){
			e.getStackTrace();
			return null;
		}finally{
			dbc.close();
		}
	}
}
